import re

from mysql import *


def hash_title(name):
    name = re.sub("[^\w]", "", name.lower())
    name = name.replace("&", "and")
    return name


def hash_artist(name):
    name = re.sub("(^|\s)the(\s|$)", "", name.lower())
    return hash_title(name)


class Author(Model):
    table_name = "author"
    fields = ("AID", "author", "ahash")
    key_name = "AID"

    @classmethod
    def get_by_name(cls, name):
        rows = cls.find({"ahash": hash_artist(name)})
        if rows:
            return rows[0]

    @classmethod
    def find_by_name(cls, name, create=False):
        author = cls.get_by_name(name)
        if author is None:
            alias = Alias.find_by_name(name)
            if alias is not None:
                author = alias.get_author()

        if author is None and create:
            author = cls(author=name)
            author.put()

        return author

    def find_aliases(self):
        return Alias.find_by_aid(self["AID"])

    @classmethod
    def get_by_id(cls, id):
        rows = cls.find({"AID": id})
        if rows:
            return rows[0]

    def put(self):
        self["ahash"] = hash_artist(self["author"])
        return super(Author, self).put()


class Alias(Model):
    table_name = "authoralias"
    fields = ("AID", "ALID", "alias", "alhash")
    key_name = "ALID"

    @classmethod
    def find_by_aid(cls, aid):
        return cls.find({"AID": aid})

    @classmethod
    def find_by_name(cls, name):
        rows = cls.find({"alhash": hash_artist(name)})
        if rows:
            return rows[0]

    def get_author(self):
        return Author.get_by_key(self["AID"])


class Lyric(Model):
    table_name = "lyrics"
    fields = ("LyID", "AID", "UID", "time", "title", "COMMENT", "album", "lyrics", "thash", "cnt", "freeze", "leaves")
    key_name = "LyID"

    @classmethod
    def find_by_title(cls, title):
        """Finds tracks by title.  Only useful for testing purposes: normal
        users specify an artist."""
        return cls.find({"thash": hash_title(title), "freeze": None})

    @classmethod
    def find_by_artist_and_title(cls, artist, title):
        artist = Author.get_by_name(artist)
        if artist is None:
            return None

        rows = cls.find({"AID": artist["AID"], "thash": hash_title(title), "freeze": None})
        if rows:
            return rows[0]

    @classmethod
    def find_since(cls, start_id, limit):
        return cls.find_by_sql("SELECT * FROM __TABLE__ WHERE `LyID` >= %u AND `freeze` IS NULL ORDER BY `LyID` LIMIT %u" % (start_id, limit))

    def get_artist_name(self):
        author = Author.get_by_id(self["AID"])
        if author:
            return author["author"]

    def get_artist_aliases(self):
        author = Author.get_by_id(self["AID"])
        if author:
            return author.find_aliases()

    def set_artist(self, artist):
        self["AID"] = artist["AID"]

    def set_user(self, user):
        if user:
            self["UID"] = user["UID"]

    def put(self):
        self["thash"] = hash_title(self["title"])
        return super(Lyric, self).put()

    def freeze_old(self):
        """Invalidates older lyrics for this song."""
        if self.get("uid") is None:
            return self.execute("UPDATE __TABLE__ SET `freeze` = 1 WHERE `AID` = ? AND `thash` = ? AND `LyID` != ? AND `uid` IS NULL", (self["AID"], self["thash"], self["LyID"]))
        else:
            return self.execute("UPDATE __TABLE__ SET `freeze` = 1 WHERE `AID` = ? AND `thash` = ? AND `LyID` != ?", (self["AID"], self["thash"], self["LyID"]))


class ClientStat(Model):
    table_name = "cst"
    fields = ("CID", "time", "addr", "author", "ahash", "title", "thash", "album", "action", "checkpoint", "got", "agent", "aver")

    def put(self):
        self["ahash"] = hash_artist(self["author"])
        self["thash"] = hash_title(self["title"])
        return super(ClientStat, self).put()


class User(Model):
    table_name = "users"
    fields = ("UID", "login", "pwd", "time", "email", "LEVEL", "setby", "expiretime", "iface_hide_abc")
    key_name = "UID"

    @classmethod
    def find_by_login_and_password(cls, login, password):
        rows = cls.find({"login": login, "pwd": password})
        if rows:
            return rows[0]
